package com.gxuwz.medical.domain.family_member;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;

import com.gxuwz.medical.database.DbUtil;
import com.gxuwz.medical.domain.area.Area;
import com.gxuwz.medical.domain.family.Family;
import com.gxuwz.medical.tool.MyTime;

public class Family_member {
	private String join_nh_code;// 农合证号
	private String familycode;// 家庭编号
	private String membername;// 姓名
	private String IDcard;// 身份证号码
	private String state;// 人员属性
	private String relation;// 与 户 主 关系
	private Timestamp createtime;
	private String userid;
	private String familyteam;// 户 内编号
	private String age;
	private String birthday;
	private String tel;
	private String health;
	private String nation;
	private String sex;
	private String IScountryside;
	private String occupation;// 职业
	private String address;
	private String medicalCard;// 医疗证卡号
	private String ch_year;
	private Area area;

	/**
	 * 自动生成农合账号编号
	 * 
	 * @return
	 * @throws SQLException
	 */
	@SuppressWarnings("unused")
	private String createJoin_nh_code() throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String familycode = this.getFamilycode();
		String sql = "select max(join_nh_code) from t_family_member where familycode like'" + familycode + "%'";

		try {
			conn = DbUtil.getConn();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			String maxcode = "";
			Integer number = 1;
			if (rs.next()) {
				maxcode = rs.getString(1);
			}

			if (maxcode != null) {
				int beginIndex = maxcode.length() - 2;
				String no = maxcode.substring(beginIndex);
				number = Integer.parseInt(no);
				++number;
				// 使用0补足位数
				no = String.format("%02d", number);
				maxcode = this.getFamilycode() + no;
			} else {
				String no = String.format("%02d", number);
				maxcode = this.getFamilycode() + no;
			}
			return maxcode;
		} catch (SQLException e) {

			throw e;
		} finally {
			DbUtil.close(rs, pstmt, conn);
		}
	}

	/**
	 * 
	 * @Title: createFamilyteam @Description: 生成组编号 @author 陈震洺 @return @throws
	 */
	@SuppressWarnings("unused")
	private String createFamilyteam() {
		String familyteam = "";
		Integer number = 1;
		try {
			familyteam = createJoin_nh_code();
			int beginIndex = familyteam.length() - 2;
			String no = familyteam.substring(beginIndex);
			number = Integer.parseInt(no);
			// ++number;
			// 使用0补足位数
			no = String.format("%02d", number);
			familyteam = no;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return familyteam;
	}

	public Family_member(String join_nh_code) throws SQLException {
		if (join_nh_code == null) {
			throw new IllegalStateException("家庭档案信息编号不能空");
		}
		try {
			this.join_nh_code = join_nh_code;
			loadDB();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 根据身份证号码查询家庭成员信息
	 * 
	 * @param IDcard
	 * @return
	 * @throws SQLException
	 */
	public Family_member(String IDcard, String join_nh_code) throws SQLException {
		if (IDcard == null) {
			throw new IllegalStateException("家庭档案信息编号不能空");
		}
		try {
			this.IDcard = IDcard;
			loadByIdCardDB();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 根据家庭编号查询家庭成员信息
	 * 
	 * @param areaid
	 * @throws SQLException
	 */
	private void loadDB() throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DbUtil.getConn();
			pstmt = conn.prepareStatement("select * from t_family_member where join_nh_code=?");
			int index = 1;
			pstmt.setString(index, this.getJoin_nh_code());
			rs = pstmt.executeQuery();
			if (rs.next()) {

				this.join_nh_code = rs.getString("join_nh_code");
				this.familycode = rs.getString("familycode");
				this.membername = rs.getString("membername");
				this.IDcard = rs.getString("IDcard");
				this.state = rs.getString("state");

				this.relation = rs.getString("relation");
				this.userid = rs.getString("userid");
				this.age = rs.getString("age");
				this.birthday = rs.getString("birthday");
				this.familyteam = rs.getString("familyteam");

				this.tel = rs.getString("tel");
				this.health = rs.getString("health");
				this.nation = rs.getString("nation");
				this.medicalCard = rs.getString("medicalCard");
				this.sex = rs.getString("sex");
				this.IScountryside = rs.getString("IScountryside");
				this.occupation = rs.getString("occupation");
				this.address = rs.getString("address");
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			DbUtil.close(rs, pstmt, conn);
		}
	}

	/**
	 * 根据身份证号码查询家庭成员信息
	 * 
	 * @param areaid
	 * @throws SQLException
	 */
	private void loadByIdCardDB() throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DbUtil.getConn();
			pstmt = conn.prepareStatement("select * from t_family_member where IDcard=?");
			int index = 1;
			pstmt.setString(index, this.getIDcard());
			rs = pstmt.executeQuery();
			if (rs.next()) {

				this.join_nh_code = rs.getString("join_nh_code");
				this.familycode = rs.getString("familycode");
				this.membername = rs.getString("membername");
				this.IDcard = rs.getString("IDcard");
				this.state = rs.getString("state");

				this.relation = rs.getString("relation");
				this.userid = rs.getString("userid");
				this.age = rs.getString("age");
				this.birthday = rs.getString("birthday");
				this.familyteam = rs.getString("familyteam");

				this.tel = rs.getString("tel");
				this.health = rs.getString("health");
				this.nation = rs.getString("nation");
				this.medicalCard = rs.getString("medicalCard");
				this.sex = rs.getString("sex");
				this.IScountryside = rs.getString("IScountryside");
				this.occupation = rs.getString("occupation");
				this.address = rs.getString("address");
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			DbUtil.close(rs, pstmt, conn);
		}
	}

	public void edit() throws SQLException {
		Connection conn = DbUtil.getConn();
		try {
			conn.setAutoCommit(false);
			this.updateToDB(conn);
			conn.commit();
		} catch (SQLException e) {
			conn.rollback();
			throw e;
		} finally {
			DbUtil.close(conn);
		}
	}

	private void updateToDB(Connection conn) throws SQLException {
		PreparedStatement pstmt = null;
		try {
			// 定义构造update SQL语句字符串变量sql
			String sql = "update t_family_member set membername=?,sex=?,nation=?,tel=?,address=? where join_nh_code=?";
			// 创建执行带动态参数的SQL的PreparedStatement pstmt
			pstmt = conn.prepareStatement(sql);
			// 设置动态参数对应的值
			int index = 1;
			pstmt.setString(index++, this.membername);
			pstmt.setString(index++, this.sex);
			pstmt.setString(index++, this.nation);

			pstmt.setString(index++, this.tel);
			pstmt.setString(index++, this.address);
			pstmt.setString(index++, this.join_nh_code);
			int count = pstmt.executeUpdate();

		} catch (SQLException e) {
			throw e;
		} finally {
			DbUtil.close(pstmt);
		}

	}

	/**
	 * 
	 * @Title: editYear @Description: 更改参合年份 @author 陈震洺 @param join_nh_code @throws
	 *         SQLException @throws
	 */
	public void editYear(String join_nh_code) throws SQLException {
		Connection conn = DbUtil.getConn();
		try {
			this.join_nh_code = join_nh_code;
			this.ch_year = MyTime.getStringNowYear();
			conn.setAutoCommit(false);
			this.updateYearToDB(conn);
			conn.commit();
		} catch (SQLException e) {
			conn.rollback();
			throw e;
		} finally {
			DbUtil.close(conn);
		}
	}

	/**
	 * 
	 * @Title: updateYearToDB @Description: 更改参合年份 @author 陈震洺 @param conn @throws
	 *         SQLException @throws
	 */
	private void updateYearToDB(Connection conn) throws SQLException {
		PreparedStatement pstmt = null;
		try {
			// 定义构造update SQL语句字符串变量sql
			String sql = "update t_family_member set ch_year=?where join_nh_code=?";
			// 创建执行带动态参数的SQL的PreparedStatement pstmt
			pstmt = conn.prepareStatement(sql);
			// 设置动态参数对应的值
			int index = 1;
			pstmt.setString(index++, this.ch_year);
			pstmt.setString(index++, this.join_nh_code);
			int count = pstmt.executeUpdate();

		} catch (SQLException e) {
			throw e;
		} finally {
			DbUtil.close(pstmt);
		}

	}

	public void del(String join_nh_code) throws SQLException {
		Connection conn = null;
		try {
			// 1:对象属性赋值
			this.join_nh_code = join_nh_code;
			conn = DbUtil.getConn();
			// 2；调用delFromDB方法
			delFromDB(conn);
		} catch (SQLException e) {
			throw e;
		} finally {
			DbUtil.close(conn);
		}

	}

	private void delFromDB(Connection conn) throws SQLException {
		PreparedStatement pstmt = null;
		try {
			// 定义构造插入SQL语句字符串变量sql
			String sql = "delete from t_family_member where join_nh_code=?";
			// 创建执行带动态参数的SQL的PreparedStatement pstmt
			pstmt = conn.prepareStatement(sql);
			// 设置动态参数对应的值
			int index = 1;
			pstmt.setString(index++, this.join_nh_code);
			int count = pstmt.executeUpdate();

		} catch (SQLException e) {
			throw e;
		} finally {
			DbUtil.close(pstmt);

		}

	}

	public void addFamily_member(String join_nh_code, String familycode, String membername, String IDcard, String state,
			String relation, String userid, String age, String birthday, String tel, String health, String familyteam,
			String nation, String sex, String IScountryside, String occupation, String address, String medicalCard)
			throws Exception {
		Connection conn = null;
		try {
			// 初始化属性
			this.familycode = familycode;
			this.join_nh_code = createJoin_nh_code();
			this.membername = membername;
			this.IDcard = IDcard;
			this.state = state;

			this.relation = relation;
			this.userid = userid;
			this.age = age;
			this.birthday = birthday;
			this.familyteam = createFamilyteam();

			this.tel = tel;
			this.health = health;
			this.nation = nation;
			this.medicalCard = createJoin_nh_code();
			this.sex = sex;
			this.IScountryside = IScountryside;
			this.occupation = occupation;
			this.address = address;
			this.createtime = MyTime.getNowForTimestamp();
			// 保存导数据库
			conn = DbUtil.getConn();
			conn.setAutoCommit(false);
			// 调用插入数据库私有方法
			this.saveToDB(conn);
			Family family = new Family();
			family.editMenber(this.getFamilycode());
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			throw e;
		} finally {
			DbUtil.close(conn);
		}

	}

	/**
	 * 插入记录
	 * 
	 * @param connection
	 * @throws SQLException
	 */
	private void saveToDB(Connection connection) throws SQLException {
		PreparedStatement pstmt = null;
		try {

			StringBuffer sqlBuff = new StringBuffer();
			sqlBuff.append(
					"insert into t_family_member(join_nh_code,familycode,IDcard,membername,state,relation,createtime,userid,familyteam,age,birthday,tel,health,nation,sex,IScountryside,occupation,address,medicalCard)");
			sqlBuff.append("values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			String sql = sqlBuff.toString();
			pstmt = connection.prepareStatement(sql);
			// 依次设置动态参数的值，注意顺序
			int index = 1;
			pstmt.setString(index++, this.join_nh_code);// 行政区域编码
			pstmt.setString(index++, this.familycode);
			pstmt.setString(index++, this.IDcard);
			pstmt.setString(index++, this.membername);

			pstmt.setString(index++, this.state);// 行政区域编码
			pstmt.setString(index++, this.relation);
			pstmt.setTimestamp(index++, this.createtime);
			pstmt.setString(index++, this.userid);
			pstmt.setString(index++, this.familyteam);
			pstmt.setString(index++, this.age);
			pstmt.setString(index++, this.birthday);

			pstmt.setString(index++, this.tel);// 行政区域编码
			pstmt.setString(index++, this.health);

			pstmt.setString(index++, this.nation);
			pstmt.setString(index++, this.sex);// 行政区域编码
			pstmt.setString(index++, this.IScountryside);
			pstmt.setString(index++, this.occupation);
			pstmt.setString(index++, this.address);
			pstmt.setString(index++, this.medicalCard);

			// 以executeUpdate()执行SQL
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {
			// 只关闭PrepareStatement资源
			if (pstmt != null) {
				pstmt.close();
			}

		}

	}

	public Family_member() {
		super();
	}

	public Family_member(String join_nh_code, String familycode, String membername, String iDcard, String state,
			String relation, String userid, String familyteam, String age, String birthday, String tel, String health,
			String nation, String sex, String iScountryside, String occupation, String address, String medicalCard) {
		super();
		this.join_nh_code = join_nh_code;
		this.familycode = familycode;
		this.membername = membername;
		IDcard = iDcard;
		this.state = state;
		this.relation = relation;
		this.userid = userid;
		this.familyteam = familyteam;
		this.age = age;
		this.birthday = birthday;
		this.tel = tel;
		this.health = health;
		this.nation = nation;
		this.sex = sex;
		IScountryside = iScountryside;
		this.occupation = occupation;
		this.address = address;
		this.medicalCard = medicalCard;
	}

	public Family_member(String join_nh_code, String familycode, String membername, String iDcard, String state,
			String relation, Timestamp createtime, String userid, String familyteam, String age, String birthday,
			String tel, String health, String nation, String sex, String iScountryside, String occupation,
			String address, String medicalCard, String ch_year) {
		super();
		this.join_nh_code = join_nh_code;
		this.familycode = familycode;
		this.membername = membername;
		IDcard = iDcard;
		this.state = state;
		this.relation = relation;
		this.createtime = createtime;
		this.userid = userid;
		this.familyteam = familyteam;
		this.age = age;
		this.birthday = birthday;
		this.tel = tel;
		this.health = health;
		this.nation = nation;
		this.sex = sex;
		IScountryside = iScountryside;
		this.occupation = occupation;
		this.address = address;
		this.medicalCard = medicalCard;
		this.ch_year = ch_year;
	}

	public String getJoin_nh_code() {
		return join_nh_code;
	}

	public void setJoin_nh_code(String join_nh_code) {
		this.join_nh_code = join_nh_code;
	}

	public String getFamilycode() {
		return familycode;
	}

	public void setFamilycode(String familycode) {
		this.familycode = familycode;
	}

	public String getMembername() {
		return membername;
	}

	public void setMembername(String membername) {
		this.membername = membername;
	}

	public String getIDcard() {
		return IDcard;
	}

	public void setIDcard(String iDcard) {
		IDcard = iDcard;
	}

	public String getState() {
		return state;
	}

	public void setState(String state) {
		this.state = state;
	}

	public String getRelation() {
		return relation;
	}

	public void setRelation(String relation) {
		this.relation = relation;
	}

	public Timestamp getCreatetime() {
		return createtime;
	}

	public void setCreatetime(Timestamp createtime) {
		this.createtime = createtime;
	}

	public String getUserid() {
		return userid;
	}

	public void setUserid(String userid) {
		this.userid = userid;
	}

	public String getFamilyteam() {
		return familyteam;
	}

	public void setFamilyteam(String familyteam) {
		this.familyteam = familyteam;
	}

	public String getAge() {
		return age;
	}

	public void setAge(String age) {
		this.age = age;
	}

	public String getBirthday() {
		return birthday;
	}

	public void setBirthday(String birthday) {
		this.birthday = birthday;
	}

	public String getTel() {
		return tel;
	}

	public void setTel(String tel) {
		this.tel = tel;
	}

	public String getHealth() {
		return health;
	}

	public void setHealth(String health) {
		this.health = health;
	}

	public String getNation() {
		return nation;
	}

	public void setNation(String nation) {
		this.nation = nation;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public String getIScountryside() {
		return IScountryside;
	}

	public void setIScountryside(String iScountryside) {
		IScountryside = iScountryside;
	}

	public String getOccupation() {
		return occupation;
	}

	public void setOccupation(String occupation) {
		this.occupation = occupation;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public String getMedicalCard() {
		return medicalCard;
	}

	public void setMedicalCard(String medicalCard) {
		this.medicalCard = medicalCard;
	}

	public String getCh_year() {
		return ch_year;
	}

	public void setCh_year(String ch_year) {
		this.ch_year = ch_year;
	}

	public Area getArea() {
		return area;
	}

	public void setArea(Area area) {
		this.area = area;
	}

}